package UserDatabase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.cj.jdbc.result.ResultSetMetaData;

public class HouseDatabase {

	Connection connect = null;
	Statement stem = null;
	ResultSet rs =null;
	
	String SQL = "";
	String columnName[];
	String record[][];
	int columnCount,recordAmount;
	
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/property_system","root","yang2532661314");
			//System.out.println(connect);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connect;
	}
	
	//更新房间信息
		public void update(House h) {
			connect = getConnection();
			try {
				stem = connect.createStatement();
				String hno = h.getHno();
				String state = h.getState();
				stem.executeUpdate("UPDATE House set state = '"+state+"' where hno = '"+hno+"'");                               
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();	
			}
			closegdbc();
		}
	
	//新增房间信息
		public void insert(House h) {
			connect = getConnection();
			try {
				stem = connect.createStatement();
				String uno = h.getUno();
				String hno = h.getHno();
				String state = "未入户";
				stem.executeUpdate("INSERT INTO House VALUES('"+hno+"','"+uno+"','"+state+"')");                               
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();	
			}
			closegdbc();
		}
			
	//通用查询
		public void setSQL(String SQL) {
		
			this.SQL = SQL.trim();
			
		}
		
		public String[] getColumnName() {
		
			return columnName;
			
		}
		
		public String[][] getRecord(){

			startQuery();
			return record;
			
		}

		public void startQuery() {
		
			connect  = getConnection();
			
			try {
			
				stem = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
				
				rs = stem.executeQuery(SQL);
				
				ResultSetMetaData metadata = (ResultSetMetaData) rs.getMetaData();
				
				columnCount = metadata.getColumnCount();
				
				columnName = new String[columnCount];
				
				for(int i=1;i<=columnCount;i++) {
				
					columnName[i-1] = metadata.getColumnName(i);
					
				}
				
				rs.last();
				
				recordAmount = rs.getRow();
				
				record = new String[recordAmount][columnCount];
				
				int i = 0;
				
				rs.beforeFirst();
				
				while(rs.next()) {
				
					for(int j=1;j<=columnCount;j++) {
					
						record[i][j-1] = rs.getString(j);
						
					}		
					i++;
				}
				closegdbc();
			} catch (SQLException e) {
			
				// TODO Auto-generated catch block
				
				e.printStackTrace();
				
			}
		}
		
		//关闭数据库
		
		public void closegdbc() {	
			try {
				if(connect!=null) {
					connect.close();	
				}
				if(rs!=null) {
					rs.close();	
				}
				if(stem!=null) {
					stem.close();	
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();	
			}	
		}
		
}
